package com.example.demo.util.excel;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

//import org.junit.Assert;

public abstract class AbstractExcelPoi<T> {

    private char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();

    public List<T> parseExcel(File file, Class<T> type) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(file, type, cellMapping, Collections.EMPTY_LIST);
    }

    public List<T> parseExcel(InputStream inputStream, Class<T> type) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(inputStream, type, cellMapping, Collections.EMPTY_LIST);
    }

    public List<T> parseExcel(File file, Class<T> type, Collection collection) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(file, type, cellMapping, collection);
    }

    public List<T> parseExcel(InputStream inputStream, Class<T> type, Collection collection) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(inputStream, type, cellMapping, collection);
    }

    public List<T> parseExcel(File file, Class<T> type, Integer... start) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(file, type, cellMapping, Collections.EMPTY_LIST, start);
    }

    public List<T> parseExcel(InputStream inputStream, Class<T> type, Integer... start) throws IOException {
        Map<String, String> cellMapping = getCellMap(type);
        return this.parseExcel(inputStream, type, cellMapping, Collections.EMPTY_LIST, start);
    }

    private Map<String, String> getCellMap(Class<T> type) {
        Map<String, String> cellMapping = new LinkedHashMap<>();
        Field[] fields = type.getDeclaredFields();
        int i = 0;
        for (Field field : fields) {
            cellMapping.put(String.valueOf(alphabet[i]), field.getName());
            i++;
        }
        return cellMapping;
    }

    public List<T> parseExcel(InputStream inputStream, Class<T> type, Map<String, String> cellMapping, Integer... start) throws IOException {
        return this.parseExcel(inputStream, type, cellMapping, Collections.EMPTY_LIST, start);
    }

    public List<T> parseExcel(File file, Class<T> type, Map<String, String> cellMapping, Integer... start) throws IOException {
        return this.parseExcel(file, type, cellMapping, Collections.EMPTY_LIST, start);
    }


    public List<T> parseExcel(InputStream inputStream, Class<T> type, Map<String, String> cellMapping, Collection collection, Integer... start) throws IOException {
        List<T> list = new ArrayList<>();
        OPCPackage container = null;
        try {
            container = OPCPackage.open(inputStream);
            if (null != start && start.length > 0 && start[0] > 0) {
                list = getResultSet(type, cellMapping, list, container, collection, start[0]);
            } else {
                list = getResultSet(type, cellMapping, list, container, collection, 0);
            }
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        } finally {
            if (null != container)
                container.close();
        }

        return list;
    }

    public List<T> parseExcel(File file, Class<T> type, Map<String, String> cellMapping, Collection collection, Integer... start) throws IOException {
        List<T> list = new ArrayList<>();
        OPCPackage container = null;
        try {
            container = OPCPackage.open(file.getAbsolutePath());
            if (null != start && start.length > 0 && start[0] > 0) {
                list = getResultSet(type, cellMapping, list, container, collection, start[0]);
            } else {
                list = getResultSet(type, cellMapping, list, container, collection, 0);
            }

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        } finally {
            if (null != container)
                container.close();
        }
        return list;
    }


    private List<T> getResultSet(Class<T> type, Map<String, String> cellMapping, List<T> list, OPCPackage container, Collection collection, int skipRows) throws IOException, SAXException, OpenXML4JException {
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(container);
        XSSFReader xssfReader = new XSSFReader(container);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            if (null != collection && !collection.isEmpty()) {
                if (collection.contains(iter.getSheetName().trim())) {
                    list = processSheet(styles, strings, stream, type, cellMapping, skipRows);

                    stream.close();
                    break;
                }
            } else {
                list = processSheet(styles, strings, stream, type, cellMapping, skipRows);

                stream.close();
                break;
            }

        }
        return list;
    }

    protected List<T> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream, Class<T> type, Map<String, String> cellMapping, int skipRows) throws IOException, SAXException {
        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        try {
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader sheetParser = saxParser.getXMLReader();
            ExcelWorkSheetHandler excelWorkSheetHandler = new ExcelWorkSheetHandler(type, cellMapping, skipRows);
            ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, excelWorkSheetHandler, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
            return excelWorkSheetHandler.getValueList();
        } catch (ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
        }
    }

    public void appendExcel(File file, List list, Class<T> type) throws IOException {
//        Assert.assertNotNull("padding data must be not null", null == list);
//        Assert.assertTrue("must be not empty list", list.size() > 0);
        List<T> origin = this.parseExcel(file, type);
        origin.addAll(list);
        String fileName = file.getAbsolutePath();
        this.generateExcel(FilenameUtils.getFullPath(fileName), origin, FilenameUtils.getBaseName(fileName));

    }

    public void generateExcel(String path, List<T> list, String fileName) throws IOException {

        Map<String, List<T>> map = new HashMap<>();
        map.put(fileName, list);
        this.generateExcel(path, map, fileName);
    }

    public void generateExcel(String path, Map<String, List<T>> map, String fileName) throws IOException {

        Workbook wb = new SXSSFWorkbook(100);
        for (String sheetName : map.keySet()) {
            Sheet sheet = wb.createSheet(sheetName);
//            Assert.assertNotNull("padding data must be not null", map.get(sheetName));
//            Assert.assertTrue("must be not empty list", map.get(sheetName).size() > 0);
            doSetHead(wb, sheet, map.get(sheetName).get(0));
            doGenerateExcel(map.get(sheetName), sheet);
        }
        String absoluteFilePath = path + File.separator + fileName + ".xlsx";
        File file = new File(absoluteFilePath);
        FileOutputStream os = new FileOutputStream(file);
        wb.write(os);
        if (wb instanceof SXSSFWorkbook) {
            SXSSFWorkbook v = (SXSSFWorkbook) wb;
            v.dispose();
        }
        os.close();
        wb.close();
    }

    public void exportExcel(HttpServletResponse response, List<T> list, String fileName) throws IOException {
        Map<String, List<T>> map = new HashMap<>();
        map.put(fileName, list);
        this.exportExcel(response, map, fileName);
    }

    public void exportExcel(HttpServletResponse response, Map<String, List<T>> map, String fileName) throws IOException {

        fileName = new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO8859_1");
        Workbook wb = new SXSSFWorkbook(100);
        for (String sheetName : map.keySet()) {
            Sheet sheet = wb.createSheet(sheetName);
//            Assert.assertNotNull("padding data must be not null", map.get(sheetName));
//            Assert.assertTrue("must be not empty list", map.get(sheetName).size() > 0);
            doSetHead(wb, sheet, map.get(sheetName).get(0));
            doGenerateExcel(map.get(sheetName), sheet);
        }
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Type", "application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        //解决 getOutputStream() has already been called for this response
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        if (wb instanceof SXSSFWorkbook) {
            SXSSFWorkbook v = (SXSSFWorkbook) wb;
            v.dispose();
        }
        byte[] outArray = outByteStream.toByteArray();
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.close();
        wb.close();
        response.flushBuffer();
    }


    public void exportExcel(HttpServletResponse response, List<T> list, List<String> head, String fileName) throws IOException {

        fileName = new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO8859_1");
        Workbook wb = new SXSSFWorkbook(100);

        Sheet sheet = wb.createSheet(fileName);
        doSetCustomHead(wb, sheet, head);


        doGenerateExcel(list, sheet);

        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Type", "application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename="
                + fileName);
        //解决 getOutputStream() has already been called for this response
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        if (wb instanceof SXSSFWorkbook) {
            SXSSFWorkbook v = (SXSSFWorkbook) wb;
            v.dispose();
        }
        byte[] outArray = outByteStream.toByteArray();
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.close();
        wb.close();
        response.flushBuffer();
    }


    public abstract void doSetHead(Workbook wb, Sheet sheet, T t);

    public abstract void doGenerateExcel(List<T> list, Sheet sheet);

    public abstract void doSetCustomHead(Workbook wb, Sheet sheet, List<String> list);
}
